/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Models;

import java.sql.*;
import java.util.Vector;

import Beans.NgonNgu;

/**
 *
 * @author Venus
 */
public class TienTe_Model {

    public Vector LayDSTT(int langid) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " Order by MaTTe desc ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static int TaoMa() {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select max(MaTTe) from tiente";
            rss = stmt.executeQuery(query);
            int maht = 0;// biến maht chứa mã của khu vực hiện tại
            while (rss.next()) {
                maht = rss.getInt(1);
            }
            stmt.close();
            rss.close();
            conn.close();
            return maht + 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean KiemTraTenTT(String currencycode) {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tiente where MaTTe = '" + currencycode + "'";
            rss = stmt.executeQuery(query);
            boolean n = false;
            if (rss.next()) {
                n = true;
            }
            stmt.close();
            rss.close();
            conn.close();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean AddTienTe(String currencycode, String currencyname, String symbols) // Tạo khu vực
    {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            NgonNgu[] dsnn = NgonNgu_Model.DSNgonNgu();
            String x = "insert into tiente values ";
            for (int i = 0; i < dsnn.length; i++) {
                x = x + "('" + currencycode + "', " + dsnn[i].getMaNN() + ", '" + currencyname + "', '" + symbols + "', 0 ),";
            }
            String query = x.substring(0, x.length() - 1);
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public Vector Detail(String matt) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tiente where MaTTe = '" + matt + "' ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTenTT(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " Order by TenTTe " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortKyHieuTT(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " Order by KyHieu " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortTTTT(int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " Order by MacDinh " + order;

            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector Search(String tentt, String kyhieu, String macdinh, int langid) {

        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " ";
            String[] arr = {tentt, kyhieu, macdinh};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(kyhieu)) {
                    query = query + " and KyHieu = " + kyhieu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(macdinh)) {
                    query = query + " and MacDinh = " + macdinh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentt)) {
                    query = query + " and TenTTe like '%" + arr[i] + "%' ";
                }
            }
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTenTT(String tentt, String kyhieu, String macdinh, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " ";
            String[] arr = {tentt, kyhieu, macdinh};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(kyhieu)) {
                    query = query + " and KyHieu = " + kyhieu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(macdinh)) {
                    query = query + " and MacDinh = " + macdinh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentt)) {
                    query = query + " and TenTTe like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by TenTTe " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchKyHieuTT(String tentt, String kyhieu, String macdinh, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " ";
            String[] arr = {tentt, kyhieu, macdinh};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(kyhieu)) {
                    query = query + " and KyHieu = " + kyhieu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(macdinh)) {
                    query = query + " and MacDinh = " + macdinh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentt)) {
                    query = query + " and TenTTe like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by KyHieu " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public Vector SortSearchTTTT(String tentt, String kyhieu, String macdinh, int langid, String order) {
        Vector v = new Vector();
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select distinct(MaTTe), MaNN, TenTTe, KyHieu, MacDinh from tiente where MaNN = " + langid + " ";
            String[] arr = {tentt, kyhieu, macdinh};
            for (int i = 0; i < arr.length; i++) {
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(kyhieu)) {
                    query = query + " and KyHieu = " + kyhieu + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(macdinh)) {
                    query = query + " and MacDinh = " + macdinh + " ";
                }
                if (!arr[i].equals("") && !arr[i].equals("all") && arr[i].equals(tentt)) {
                    query = query + " and TenTTe like '%" + arr[i] + "%' ";
                }
            }
            query = query + " Order by MacDinh " + order;
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                Beans.TienTe ttb = new Beans.TienTe();
                ttb.setMaTTe(rss.getString(1));
                ttb.setMaNN(rss.getInt(2));
                ttb.setTenTTe(rss.getString(3));
                ttb.setKyHieu(rss.getString(4));
                ttb.setMacDinh(rss.getByte(5));
                v.add(ttb);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.getMessage();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return v;
    }

    public static boolean Delete(String matte) {
        Connection conn = null;

        Statement stmt = null;

        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Delete from tiente where MaTTe = '" + matte + "' ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean Edit(String matiente, String TenTTe, String KyHieu, String mann) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query1 = "Update tiente set TenTTe = '" + TenTTe + "'  "
                    + " where MaTTe = '" + matiente + "' and MaNN = " + mann;
            String query2 = "Update tiente set KyHieu = '" + KyHieu + "'  "
                    + " where MaTTe = '" + matiente + "' " ;
            int n = stmt.executeUpdate(query1);
            stmt.executeUpdate(query2);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean KiemTraEditTenTT(String currencycode) {

        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "select * from tiente where MaTTe != '" + currencycode + "'";
            rss = stmt.executeQuery(query);
            boolean n = false;
            if (rss.next()) {
                n = true;
            }
            stmt.close();
            rss.close();
            conn.close();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean UpdateStatusTT(String matte, String status) {
        Connection conn = null;

        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = "Update TienTe set MacDinh = " + status + " where MaTTe = '" + matte + "' ";
            int n = stmt.executeUpdate(query);
            stmt.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();

            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }

            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static boolean CheckTienTe(String matte, int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            int n = 0;
            String query = "select * from tiente where MaNN = " + langid + " and MaTTe != '" + matte + "' and MacDinh = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                n = n + 1;
            }
            stmt.close();
            rss.close();
            conn.close();
            if (n == 0) {
                return false;// thất bại
            }
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public static String LayKyHieuTT(int langid) {
        Connection conn = null;
        ResultSet rss = null;
        Statement stmt = null;
        String maht = "";
        try {
            ConnectionPool pool = new ConnectionPool();
            conn = pool.getConnection();
            stmt = conn.createStatement();
            String query = " select KyHieu from tiente where MaNN = " + langid + " and MacDinh = 1 ";
            rss = stmt.executeQuery(query);
            while (rss.next()) {
                maht = rss.getString(1);
            }
            stmt.close();
            rss.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                }
            }
            if (rss != null) {
                try {
                    rss.close();
                } catch (SQLException e) {
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                }
            }
        }
        return maht;
    }
}
